エンジニア向けのBIツール、QuaryをBigQueryに接続して使ってみた
2024.05.20こんちには。
データアナリティクス事業本部 機械学習チームの中村(nokomoro3)です。
Quaryというエンジニア向けのBIツールが気になったので使ってみました。
VSCodeの拡張機能やCLIが準備されており、以下のことができます。
- データベースへの接続
- dbtに類似した機能
- sqlファイルとしてmodelを記述可能、schemaもyamlファイルで記述可能
- schemaはGUIで操作もでき、リネージも表示可能
- また簡単なグラフなどもyamlファイルで管理可能
対応ソースはBigQueryやSnowflakeなどとなっていますので、今回はBigQueryで試してみようと思います。
BigQuery側の準備
前準備として、以下のページにある ml-latest-small.zip のデータの一部をBigQueryにアップロードします。
この中で ratings.csv と movies.csv をBigQueryの ml_latest_small データセットにそれぞれ ratings と movies テーブルとして登録しておきます。
Quaryのセットアップ
Quaryのインストール手順は何種類かあるようですが、Windowsの場合は以下から実行ファイルを落としておくことでセットアップできました。
ダウンロードじた実行ファイルにPATHを通して、以下で実行を確認します。
quary --version # => quary 0.1.2
またquaryは基本的にVSCodeの拡張機能から使いますので、以下の拡張機能をVSCodeにインストールします。
プロジェクト作成とBiqQueryとの接続
仕組み上、プロジェクト作成とBiqQueryへの接続を同時にセットアップしていく形となります。
空のフォルダを作成して、VSCodeをそこで立ち上げましょう。
mkdir -p example cd example/ code .
「Ctrl + Shift + P」などでコマンドパレットを立ち上げ、「QUARY: Initialise project」を選択します。
接続先には「BigQuery」を選択します。
ダイアログに従うと、ブラウザ上のログイン画面に遷移します。
問題ないかを確認して続行します。
Quaryの画面に遷移して、アクセストークンが発行されますのでコピーします。
VSCodeに戻り、右下の「Proceed」を押下します。
VSCodeの上部に以下の入力が表示されますので、コピーしたアクセストークンを貼り付けて「Enter」を押下します。
接続するプロジェクトとデータセットを選択して、「Create Project」を押下します。
stagingにimportするリソースを選択して、「Import sources」を押下します。
こちらで準備は完了です。
このようにVSCode拡張機能からのアクセスは、QuaryからのアクセスをGoogleアカウントで許可し、Quary側からTokenが発行されるのでそちらを使ってアクセスするようです。
そのため、アクセス権限を削除する場合は、Googleアカウントの画面から行う必要があります。
（「アクセス権限を削除」で後述）
なお、CLIを使う場合はGoogle Cloudのサービスアカウントを作成する必要がありそうです。
（こちらは別途記事にしようと思います）
プロジェクトの確認
以下のようなファイルがフォルダに作成されています。少しdbtに似ていますね。
staging/schema.yamlには以下のようなものが記載されています。
sources:
- name: raw_movies
path: {プロジェクト名}.ml_latest_small.movies
columns:
- name: genres
- name: movieId
- name: title
- name: raw_ratings
path: {プロジェクト名}.ml_latest_small.ratings
columns:
- name: movieId
- name: rating
- name: timestamp
- name: userId
models:
- name: stg_movies
description: Replace this with your description for movies
columns:
- name: genres
- name: movieId
- name: title
- name: stg_ratings
description: Replace this with your description for ratings
columns:
- name: movieId
- name: rating
- name: timestamp
- name: userId
モデルの追加
せっかくなのでmodelを追加してみようと思います。以下のようなクエリを書いてみました。
(models/rating_mean_per_user_selected.sqlとして保存)
-- 10ユーザを抽出
WITH selected_users AS (
SELECT DISTINCT userId FROM q.stg_ratings ORDER BY userId LIMIT 10
)
-- ユーザ毎のratingの平均を計算
SELECT
stg_ratings.userId,
avg(stg_ratings.rating) AS rating_mean
FROM q.stg_ratings
INNER JOIN selected_users ON stg_ratings.userId = selected_users.userId
GROUP BY stg_ratings.userId
このsqlファイルを開くと、VSCodeの拡張機能によって右上にメニューが追加されています。
起動できるメニューは左から順に以下の３つとなっています。
- Open Model Documentation
- Execute Model SQL
- Run Model tests
拡張機能からの操作
Open Model Documentation
おもにschema.yamlをリッチなGUIで編集するための機能のようです。リネージなども確認することができます。
（Ctrl + Dで起動できますが、マルチカーソルと被ってしまうのでご注意ください）
descriptionのところを編集すると、説明を追加できます。
「Add」を押下するとテストを追加することも可能です。ここではuserIdにunique制約を追加してみます。
以上をGUIから設定して保存すると、モデルファイルと同じところにschema.yamlが作成されます。
models:
- name: rating_mean_per_user_selected
columns:
- name: rating_mean
description: rating mean per userId
- name: userId
description: user identifier
tests:
- type: unique
テストは以下から選択可能することが可能なようです。（dbtでもおなじみのSingular Testのものが多いですね）
- unique
- not_null
- gt (greater than)
- gte (greater than equal)
- lt (less than)
- lte (less than equal)
- relationship
- accepted_values
Execute Model SQL
こちらは拡張機能上でクエリを実行することができます。結果のダウンロードなども可能なようです。
Run Model tests
こちらは先ほど設定したテストを実行した際の結果を確認できます。
quary testを実行した後に再度確認します。
チャートの追加
chart.yamlというsuffixを持つファイルを作成すると、簡単なグラフを作成することができます。
（chart.ymlというsuffixではNGなので注意）
新規ファイルとして sample.chart.yaml を作成して開くと、以下のようなAsset選択画面となります。
Assetから先ほど作成したrating_mean_per_user_selectedを選択して、右にある「▶」を押下します。
（押下後ずっとロード中となる場合は、VSCode自体を再起動してみてください。少し不安定なようです。）
ロード後は以下のような画面となります。最初はDatagridという状態となり、右上のconfigureからグラフを変更することができます。
configureを選択すると以下のような画面となります。一番上をクリックするとグラフ種類が表示されます。
（configureボタン押下時に表示される右メニューは挙動が少し不安定ですので、消えた場合は再度configureを押下されてください）
以下のようなグラフ種類が表示されますので、Y Barを選択します。
色々カラムなどの選択を操作してユーザ毎のrating_meanの棒グラフを作成しました。
保存後、右上の編集ボタンでchart.yamlの中身を確認できます。
今回は以下のような内容となっており、グラフもファイルとして保存することができます。
config:
aggregates: {}
columns:
- userId
columns_config: {}
expressions: {}
filter: []
group_by: []
plugin: Y Bar
plugin_config: {}
settings: true
sort: []
split_by: []
theme: Pro Light
title: "{chart.yamlファイルのパス}"
version: 2.10.0
reference:
name: rating_mean_per_user_selected
アクセス権限を削除
ブラウザから以下のURLにアクセスします。
- https://myaccount.google.com/data-and-privacy
ここで「サードパーティ製のアプリとサービス」を選択して遷移します。
この中からQuaryを選択します。
以下のような画面となりますので、ページ下部の「Quaryとの接続をすべて削除しますか？」をクリックします。
ダイアログがでますので、確認を押下します。
まとめ
いかがでしたでしょうか。今回はQuaryというエンジニア向けのBIツールを試してみました。
簡易なグラフを書くケースだとdbtのように使えてフィットするのかなと感じました。
今後も情報があればブログにしたいと思います。
